
use strict;
use XML::LibXML;
use LWP::Simple;
use DBI;
use Config::IniFiles;

# This program read config.xml, get the SOS data file, parse it, then insert the data to the database. It will automatically update platform, sensor,sensor_type,obs_type,m_type,m_scalar_type,uom_type tables if no related record found. But it can't do the unit conversion yet.

#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');

#for sqlite database
#my $timevalue="datetime('now')";
#my $dbname="waves.db";
#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
#                    { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}

#for postgres database
#establish database connection
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 

my $timevalue="current_timestamp";

my $missing_value = '-99999';

##################
#read input files for looping through calls
##################

my $target_dir = '/tmp/response';

my $config_filepath = '/var/myscheduler/config.xml';
my $config = XML::LibXML->new->parse_file($config_filepath);

=comment
my $datetime_start = `date +%Y-%m-%dT%H:%M:00Z`;
chomp($datetime_start);
my $datetime_stop = `date +%Y-%m-%dT%H:%M:00Z --date='-10 hours'`;
chomp($datetime_stop);
=cut

#print "datatime_start=$datetime_start datetime_stop=$datetime_stop\n";
#exit 0;
my $event_time = '';
#my $event_time = "$datetime_stop/$datetime_start";
#print $event_time."\n";
#exit 0;

my $counter=0;

foreach my $provider ($config->findnodes('//provider')) {
	my $sos_url = $provider->findvalue('sosURL'); 
	#print "sos_url:$sos_url\n";
        my $provider_url=$provider->findvalue('url');
        #print "provider url: $provider_url"; 
	my $platformIdPrefix = $provider->findvalue('platformIdPrefix');
	my $event_time = $provider->findvalue('dataTime'); 
	#print "platformIdPrefix:$platformIdPrefix\n";

  foreach my $node_ref_observedProperty ($provider->findnodes('observedProperty')) {
	my $observedProperty = $node_ref_observedProperty->to_literal; 
	#print "observedProperty:$observedProperty\n";

     foreach my $node_ref_platformId ($provider->findnodes('platformId')) {
	my $platformId = $node_ref_platformId->to_literal;
	$platformId = $platformIdPrefix.$platformId;
	#print "platformId:$platformId\n";
	        
        my $xml_url = $sos_url."&observedProperty=$observedProperty&offering=$platformId";
        if ($event_time ne '') { $xml_url .= "&eventTime=$event_time"; }
        #print $xml_url."\n";

        #generate a random file handle concatenating a random number
        my $xml_filepath = "$target_dir/".$platformId.$observedProperty."latest.xml";
        #print $xml_filepath."\n";

        
        my $content = getstore($xml_url, $xml_filepath);
        print "Couldn't get $xml_url" unless defined $content; 

                 

        ###############################
        #sos to hash

        my $xp = XML::LibXML->new->parse_file($xml_filepath);


        my %latest_obs = ();
        my $r_latest_obs = \%latest_obs;

        my @array_temp;

        my ($operator_name,$operator_url,$platform_handle,$platform_url,$platform_desc);
        my ($m_lon,$m_lat);
        my ($obs_type,$uom_type,$m_value,$m_date);

        #process xml to vars

        
        
        $operator_name = $xp->findvalue('//ioos:Organization'); 
         
        
        $operator_name = sprintf("%s", $operator_name);
        $operator_name=lc($operator_name);
        #@array_temp = split(/\./, $operator_name);
        #$operator_name = @array_temp[-1]; #get last part
        #print "operator_name:".$operator_name."\n";

        my $platform_id = $xp->findvalue('//ioos:StationId');
        $platform_id = sprintf("%s", $platform_id);
        my @array_temp = split(/\:/, $platform_id);
        $platform_id = @array_temp[-1]; #get last part

        $platform_handle = $operator_name.".".$platform_id;
        print "platform_handle:".$platform_handle."\n";

        $platform_desc = $xp->findvalue('//ioos:StationName');
        #$platform_desc = sprintf("%s", $platform_desc);
        #my @array_temp = split(/\./, $platform_desc);
        #$platform_desc = @array_temp[-1]; #get last part

        my $pos = $xp->findvalue('//gml:pos');
        $pos = sprintf("%s", $pos);
        ($m_lat,$m_lon) = split(/ /, $pos);
        #print "lat:$m_lat lon:$m_lon\n";

        #assign vars to hash
        $latest_obs{platform_list}{$platform_handle}{name} = $platform_handle;
        $latest_obs{platform_list}{$platform_handle}{desc} = $platform_desc;
        $latest_obs{platform_list}{$platform_handle}{m_lon} = $m_lon;
        $latest_obs{platform_list}{$platform_handle}{m_lat} = $m_lat;


        ##check whether the organization or platform exist in the database

	my $sql = qq{ SELECT row_id,short_name from organization where short_name like '$operator_name' };
	#print $sql."\n";
	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	my ($organization_row_id,$organization_name_lkp) = $sth->fetchrow_array;
        
	if ($organization_name_lkp) {} #print "$organization_name_lkp\n"; 
	else {
		print "organization $operator_name not found - inserting\n";

		$sql = qq{ SELECT max(row_id) from organization  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($organization_row_id) = $sth->fetchrow_array;	        
                $organization_row_id=$organization_row_id+1;
		$sql = qq{ INSERT into organization(row_id,row_entry_date,short_name,url) values ($organization_row_id,$timevalue,'$operator_name','$provider_url'); };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         #       print $sql."\n";
	}

	##platform####################
	$sql = qq{ SELECT row_id,short_name,platform_handle from platform where organization_id = $organization_row_id and short_name = '$platform_id' };
	#print $sql."\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();
	my ($platform_row_id,$platform_name_lkp,$newplatform_handle) = $sth->fetchrow_array;

	if ($platform_name_lkp) {
            $platform_handle=$newplatform_handle;
        } #print "$platform_name_lkp\n"; }
	else {
		print "platform $platform_id not found - inserting\n";

		#requery for new row_id
		$sql = qq{ SELECT max(row_id) from platform  };
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		($platform_row_id) = $sth->fetchrow_array;
                $platform_row_id++;
                my $undefined_platform_type_id=6;
                #$platform_desc=~ s/,//g;
                my $platform_url=$provider_url."/station_page.php?station=$platform_id";
		$sql = "INSERT into platform(row_id,row_entry_date,organization_id,type_id,short_name,platform_handle,fixed_longitude,fixed_latitude,description,url) values ($platform_row_id,$timevalue,$organization_row_id,$undefined_platform_type_id,'$platform_id','$platform_handle',$m_lon,$m_lat,'$platform_desc','$platform_url')";
		#print $sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
                $sth->finish();
         #       print $sql."\n";
	}


        #get row_id from multi_obs table, should set the multi_obs table row_id autoincrement
        if($counter==0){
                      $sql="select max(row_id) from multi_obs";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      ($counter) = $sth->fetchrow_array;
                      $counter++;
        }  

        ##############
 
        foreach my $value_component ($xp->findnodes('//ioos:Array/gml:valueComponents/ioos:Composite/gml:valueComponents')) {

             $m_date = $value_component->findvalue('ioos:CompositeContext/gml:valueComponents/gml:TimeInstant/gml:timePosition');
#print $m_date."\n";



             foreach my $quantity ($value_component->findnodes('ioos:CompositeValue/gml:valueComponents/ioos:Quantity')) { 
	         $m_value = $quantity->to_literal;
	         if (!($m_value)) { next; }
                 $obs_type = $quantity->getAttribute('name');

                 $obs_type=getDatatype($obs_type);
                 
                 $uom_type = $quantity->getAttribute('uom');
                 #print "m_date:$m_date obs_type:$obs_type uom_type:$uom_type m_value:$m_value\n";
	#last; #for the current setups, only one obs_type per xml response so only need first example

                 # get m+type_id
                 my ($m_type_id,$uom_name) = getMtype($dbh,$obs_type,$uom_type,$m_value);
                 
                 # get sensor id
                 my ($sensor_id)=getSensor($dbh,$platform_row_id,$m_type_id,$obs_type,$timevalue);
  
                if(($uom_name eq $uom_type)||($uom_type eq "1")){
                      #check duplicate record in database
                      $sql="select row_id from multi_obs where m_date='$m_date' and sensor_id=$sensor_id and m_type_id=$m_type_id";
                      #print "$sql\n";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      my ($row_id) = $sth->fetchrow_array;
                      if($row_id>0){;}
                      else {
                          #print "$row_id\n";

                          $sql="INSERT into multi_obs(row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$m_date',$m_lon,$m_lat,$missing_value,$m_value)";
                          $sth = $dbh->prepare($sql);
                          $sth->execute();
                          # print "$sql\n";
                        #print $m_type_id."\tobs".$obs_type."\tmdate".$m_date."\n";
                          $counter++;
                      }
                }
                else {
                     print "Can not find matched unit for $obs_type uom:$uom_name xml:$uom_type\n";
                }
                 
                 

            } #end of $quantity


            foreach my $quantity ($value_component->findnodes('ioos:CompositeValue/gml:valueComponents/ioos:NamedQuantityList')) { 
	         $m_value = $quantity->to_literal;
	         if (!($m_value)) { next; }
                 $obs_type = $quantity->getAttribute('name');
                 $obs_type=getDatatype($obs_type);
                 $uom_type = $quantity->getAttribute('uom');
                 #print "m_date:$m_date obs_type:$obs_type uom_type:$uom_type m_value:$m_value\n";
	#last; #for the current setups, only one obs_type per xml response so only need first example
                 # get m+type_id
                 my ($m_type_id,$uom_name) = getMtype($dbh,$obs_type,$uom_type,$m_value);
                 # get sensor id
                 #print "type id is $m_type_id\n";
                 my ($sensor_id)=getSensor($dbh,$platform_row_id,$m_type_id,$obs_type,$timevalue);
               

                if(($uom_name eq $uom_type)||($uom_type eq "1")){
                      #check duplicate record in database
                      $sql="select row_id from multi_obs where m_date='$m_date' and sensor_id=$sensor_id and m_type_id=$m_type_id";
                      $sth = $dbh->prepare($sql);
                      $sth->execute();
                      my ($row_id) = $sth->fetchrow_array;
                      #print "\nrow id is: $row_id\n";
                      if($row_id>0){;}
                      else {
                          $sql="INSERT into multi_obs(row_id,row_entry_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value_list) values ($counter,$timevalue,'$platform_handle',$sensor_id,$m_type_id,'$m_date',$m_lon,$m_lat,$missing_value,'$m_value')";
                          $sth = $dbh->prepare($sql);
                          print $m_type_id."\tobs".$obs_type."\tmdate".$m_date."\n";
                          $sth->execute();
                          $counter++;
                          
                      }
                }
                else {
                     print "Can not find matched unit for $obs_type $uom_name-$uom_type\n";
                }

            } #end of $NamedQuantityList

       } # end of value component
       $sth->finish();
    } #foreach platformId
  } #foreach observedProperty
} #foreach provider

 
 $dbh->disconnect();

exit 0;

##############################################

sub escape_literals {

my $platform_url = shift;
$platform_url =~ s/&/&amp;/ ;

return $platform_url;

}


sub getDatatype{
 my ($s)=@_;
 
 my $new_string="";
 my $t;
 for(my $i=0;$i<length($s);$i++){
   $t=substr($s,$i,1);
   if($t lt "a"){
      if($i==0){
         $t=lc($t);
      }
      else{
        $t="_".lc($t);
      } 
   }

   $new_string=$new_string.$t;
 }
 return $new_string;
}

sub getMtype{
     my ($dbh,$obs_type,$uom_type,$m_value)=@_;
     my $sql="select m_type.row_id,uom_type.display from m_type 
              left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
              left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
              left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
              where obs_type.standard_name='$obs_type'";
                #print "\n"."$sql\n";
     my $sth = $dbh->prepare($sql);
        $sth->execute();
     my ($m_type_id,$uom_name) = $sth->fetchrow_array;
     
     # if $obs_type is not available in obs_type, insert into obs_type,$uom_type,$m_scalar_type,$m_type table
     if(!$m_type_id){

         #updtae obs_type table
         $sql="select row_id from obs_type where standard_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($obstype_id) = $sth->fetchrow_array;
         if(!$obstype_id){
           $sql="select max(row_id) from obs_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($obstype_id) = $sth->fetchrow_array;
           $obstype_id++;
           $sql="insert into obs_type(row_id,standard_name) values($obstype_id,'$obs_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
         }

         #updtae uom_type table
         $sql="select row_id from uom_type where display='$uom_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($uom_id) = $sth->fetchrow_array;
         if(!$uom_id){
           $sql="select max(row_id) from uom_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($uom_id) = $sth->fetchrow_array;
           $uom_id++;
           $sql="insert into uom_type(row_id,standard_name,display) values($uom_id,'$uom_type','$uom_type')";
           $sth = $dbh->prepare($sql);
           print "$sql\n";
           $sth->execute();
           $sth->finish();
           $uom_name=$uom_type;
          }

         #updtae m_scalar_type table
         $sql="select row_id from m_scalar_type where obs_type_id=$obstype_id and uom_type_id=$uom_id";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($mscalar_id) = $sth->fetchrow_array;
         if(!$mscalar_id){
           $sql="select max(row_id) from m_scalar_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($mscalar_id) = $sth->fetchrow_array;
           $mscalar_id++;
           $sql="insert into m_scalar_type(row_id,obs_type_id,uom_type_id) values($mscalar_id,$obstype_id,$uom_id)";
           print "$sql\n";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           $sth->finish();
          }

          #update mtype table   
          $sql="select max(row_id) from m_type";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          my ($m_type_id) = $sth->fetchrow_array;
          $m_type_id++;
          $sql="insert into m_type(row_id,num_types,description,m_scalar_type_id) values($m_type_id,1,'$obs_type',$mscalar_id)";
          #print "$sql\n";
          $sth = $dbh->prepare($sql);
          $sth->execute();
          $sth->finish();
     }
     else {
          # do unit conversion
          ;
     }
     $sth->finish();
     return ($m_type_id,$uom_name);
}

sub getSensor{
  
    my ($dbh,$platform_row_id,$m_type_id,$obs_type,$timevalue)=@_;
   my $sql="select row_id from sensor where platform_id=$platform_row_id and m_type_id=$m_type_id";
    #print $sql."\n";
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    my ($sensor_id) = $sth->fetchrow_array;

    if(!$sensor_id){
        # update sensor type table
         $sql="select row_id from sensor_type where type_name='$obs_type'";
         $sth = $dbh->prepare($sql);
         $sth->execute();
         my ($type_id) = $sth->fetchrow_array;
         if(!$type_id){
           $sql="select max(row_id) from sensor_type";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($type_id) = $sth->fetchrow_array;
           $type_id++;
           $sql="insert into sensor_type(row_id,type_name,description) values($type_id,'$obs_type','$obs_type')";
           $sth = $dbh->prepare($sql);
           #print "$sql\n";
           $sth->execute();
           $sth->finish();
         }
         #insert new record to sensor table
           $sql="select max(row_id) from sensor";
           $sth = $dbh->prepare($sql);
           $sth->execute();
           my ($sensor_id) = $sth->fetchrow_array;
           $sensor_id++;
           $sql="insert into sensor(row_id,row_entry_date,platform_id,type_id,m_type_id) values ($sensor_id,$timevalue,$platform_row_id,$type_id,$m_type_id)";
           $sth = $dbh->prepare($sql);
          # print "inserting to sensor table $sql\n";
           $sth->execute();
           $sth->finish();
    }
    $sth->finish();
    return $sensor_id;
}
